from time import sleep

import scrapy
from scrapyDemo.items import ScrapydemoItem
from scrapyDemo.logg import logger
import datetime
import pymysql
import json
class BliblihotlistSpider(scrapy.Spider):
    name = 'blibli_user'

    def sql_list(self):
        connect = pymysql.connect(
            host='你的数据库ip', port=3306, user='数据库账号', password='数据库密码', database='数据库', use_unicode=True)
        # 通过cursor执行增删查改
        cursor = connect.cursor()
        starttime = datetime.date.today()
        insert_sql = f"SELECT hotID FROM `bliblihotlist` WHERE `hotTime` = '{starttime}'"
        cursor.execute(insert_sql)
        sqlList = cursor.fetchall()
        urlID = [i[0] for i in sqlList]
        return urlID

    def start_requests(self):
        proxies = {
            "http": "http://localhost:5000",
            "https": "http://localhost:5000"
        }

        url_list = self.sql_list()
        # url_list = ['guochuang']
        for num in url_list:
            url = 'https://api.bilibili.com/x/space/acc/info?mid=%s&jsonp=jsonp'%(num)
            yield scrapy.Request(url=url,callback=self.parse)

    # 转换列表或字符串含万字的
    def wan_in_str(self, string):
        try:
            if type(string) is int:
                return string
            elif string is None or len(string) == 0:
                return 0
            elif type(string) is str:
                if '万' in string:
                    string = int(float(string.split('万')[0]) * 10000)
                    return string
                elif 'w' in string:
                    string = int(float(string.split('w')[0]) * 10000)
                    return string
                elif '亿' in string:
                    string = int(float(string.split('w')[0]) * 100000000)
                    return string
                else:
                    return int(string)
            elif type(string) is list:
                if '万' in string[0]:
                    string = int(float(string[0].split('万')[0]) * 10000)
                    return string
                elif 'w' in string[0]:
                    string = int(float(string[0].split('w')[0]) * 10000)
                    return string
                else:
                    return int(string[0])
        except Exception as e:
            print('万字转换失败。。。{}:{}'.format(string, type(string)))

    def parse(self, response):
        all = json.loads(response.text)
        item = ScrapydemoItem()
        try:
            mid = all.get('data','').get('mid','') if all.get('data','').get('mid','') else ''
            item['userUrl'] = 'https://space.bilibilall.com/%s'%(mid)
            item['userMid'] = str(mid)
            item['userImage'] = all.get('data', '').get('face', '') if all.get('data', '').get('face', '') else ''
            item['userName'] = all.get('data', '').get('name', '') if all.get('data', '').get('name', '') else ''
            item['userSex'] = all.get('data', '').get('sex', '') if all.get('data', '').get('sex', '') else ''
            url = 'https://api.bilibili.com/x/relation/stat?vmid=%s&jsonp=jsonp'%(mid)
            sleep(1)
            yield scrapy.Request(url=url,callback=self.two_parse,meta={'item':item})
        except Exception as e:

            logger.error(f"{response.url}访问页面失败：{e}")

    def two_parse(self,response):
        item = response.meta['item']
        response = json.loads(response.text)
        item['userFans'] = response.get('data', '').get('follower', '') if response.get('data', '').get('follower', '') else 0
        item['userFollower'] = response.get('data', '').get('following', '') if response.get('data', '').get('following', '') else 0
        sleep(1)
        return item


